<?php

namespace app\services\data\stat\ad\normal;

class StatRoleKeep extends StatKeep
{
    public function __construct($params)
    {
        parent::__construct($params);
    }

    public function getSqlByGroup()
    {
        $ret = $this->getRetention();
        if (!$ret) {
            return false;
        }

        $selectsums_list = $ret['selectsums_list'];
        $retentions_list = $ret['retentions_list'];
        $reg_days_list   = $ret['reg_days_list'];

        $join             = $this->queryJoin('users');
        $user_where       = $this->queryWhere(['game_id', 'channel', 'game_channel_id', 'create_time', 'device_type'], 'users');
        $plan_where       = $this->queryWhere(['media_id', 'media_docking_id', 'owner', 'plan_group_id', 'plan_id', 'source_type'], 'ad_plan');
        $media_user_where = $this->queryWhere(['daili', 'media_user_id'], 'ad_media_user');

        list($select_str, $groupby_str) = $this->queryGroupBy();
        $user_select_str = $this->userSelect();
        if ($user_select_str != '') {
            $user_select_str = ", {$user_select_str}";
        }

        // 如果大于1个月此语句性能更好
        $sqls = [];
        foreach ($selectsums_list as $id => $selectsums_str) {
            $retentions_str = $retentions_list[$id];
            $min_reg_time   = $reg_days_list[$id][0];
            $max_login_time = $reg_days_list[$id][2];

            if ($select_str == '') {
                $sub_select_str = " events.role_unique_id";
            } else {
                $sub_select_str = "events.role_unique_id, {$select_str}";
            }

            $total_groupby_str = '';
            if ($groupby_str == '') {
                $groupby_select = "{$selectsums_str}";
                $sub_groupby_str = "role_unique_id";
            } else {
                $groupby_select = "{$groupby_str}, {$selectsums_str}";
                $sub_groupby_str = "role_unique_id, {$groupby_str}";
                $total_groupby_str = "GROUP BY {$groupby_str}";
            }

            $sql = "
                SELECT
                {$groupby_select}
                FROM(
                    SELECT
                    {$sub_select_str}, {$retentions_str}
                    FROM sdk.events
                    JOIN(
                        SELECT `#user_id`, `#create_time`, firstups.*, ad_plan.*, ad_media_user.* {$user_select_str} FROM sdk.users_local AS users
                        {$join}
                        WHERE {$user_where}
                        AND {$plan_where}
                        AND {$media_user_where}
                    ) AS users
                    ON users.`#user_id` = events.`#user_id`
                    WHERE events.`#event_id` IN({$this->firstEventId}, {$this->secondEventId})
                    AND events.`#create_time` >= {$min_reg_time}
                    AND events.`#create_time` < {$max_login_time}
                    GROUP BY {$sub_groupby_str}
                )
                {$total_groupby_str}
            ";

            $sqls[] = $sql;
        }

        return $sqls;
    }
}
